<?php
class GameFile extends CActiveRecord
{
    public function getGameInfo($gameId)
    {
        if($gameId == 181){
            $sql = "SELECT id, title, alias, introtext FROM c_game WHERE id=" . $gameId; 
        } else {
            $sql = "SELECT id, title, alias, introtext FROM c_game WHERE id=" . $gameId . " AND status=1";
        }
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }         
    public function getGameFileByExt($gameId,$extension){
        $sql = "SELECT id,gameId,filename,extension,create_date FROM c_game_file 
        WHERE gameId = ".intval($gameId)." AND extension='".$extension."'";

        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }
    
     public function getAllGameFileByExt($gameId,$extension){
        $sql = "SELECT id,gameId,filename,extension,create_date FROM c_game_file 
        WHERE gameId = ".intval($gameId)." AND extension='".$extension."'";

        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    
    public function getGameFile($gameId, $os)
    {
        $condition = "";
        if(!empty($os)){
            $condition .= $os=="j2me" ? " AND extension='jar'" : "";
            $condition .= $os=="android" ? " AND extension='apk'" : "";
            $condition .= $os=="ios" ? " AND extension='plist'" : "";
        }
        
        $sql = "SELECT id, filename, extension, filesize,introtext FROM c_game_file WHERE gameId=" . $gameId . "  AND status=1" . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $list_file = "0";
        foreach($rows as $row){
            $list_file .= ", " . $row["id"];    
        }
            
        $sql = "SELECT t1.gameFileId, t2.manufacturer, t2.handset FROM c_game_file_mobile_handset t1 LEFT JOIN c_mobile_handset t2 ON (t2.id = t1.mobileHandsetId) WHERE t1.gameFileId IN (" . $list_file . ")";        
        $command = Yii::app()->db->createCommand($sql);
        $rowsHandset = $command->queryAll();        
        $replaceHandset = array();
        
        foreach($rowsHandset as $row){        
            $replaceHandset[$row["gameFileId"]][] = $row["manufacturer"] . " " . $row["handset"];
        }
        
        $i = 0;
        while(isset($rows[$i])){
            $rows[$i]["handset"] = isset($replaceHandset[$rows[$i]["id"]]) ? $replaceHandset[$rows[$i]["id"]] : array();
            $i++;
        }
        
        return $rows;
    }
    
      public function getGameFileByExtension($gameId, $extension)
    {
        $condition ="";
        if(!empty($extension))
        $condition = " AND extension='".$extension."'" ;
        $sql = "SELECT id, filename, extension, filesize,introtext FROM c_game_file WHERE gameId=" . $gameId . "  AND status=1" . $condition;
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        return $rows;
    }
    
    public function getGameFileById($fileId)
    {
        $sql = "SELECT id, gameId, filename, extension,create_date FROM c_game_file WHERE id=" . $fileId;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;   
    }    
    
    public function getUserInfo($userId)
    {
        $sql = "SELECT id, username FROM c_user WHERE id=" . $userId; 
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }
    
    public function getFile($gameId, $branch, $handset, $os)
    {
        $filename = "";
        $handset1 = mb_strtoupper(str_replace(" ", "", $handset));
        $handset2 = str_replace(" ", "", $handset);
        
        $sql = "SELECT id FROM c_mobile_handset WHERE manufacturer='" . mysql_escape_string($branch) . "' AND (handset='" . mysql_escape_string($handset1) . "' OR handset='" . mysql_escape_string($handset2) . "')";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        if(!empty($rows)){
            $mobileHandsetId = $rows["id"];
            
            $sql = "SELECT id FROM c_game_file WHERE gameId=" . $gameId . " AND status=1";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $list_game_file = "0";
            foreach($rows as $row){
                $list_game_file .= "," . $row["id"];
            }
            
            $sql = "SELECT gameFileId FROM c_game_file_mobile_handset WHERE gameFileId IN (" . $list_game_file . ") AND mobileHandsetId=" . $mobileHandsetId;
            $command = Yii::app()->db->createCommand($sql);
            $rowsGameFile = $command->queryAll();
            $list_game_file = "0";
            foreach($rowsGameFile as $row){
                $list_game_file .= "," . $row["gameFileId"];
            }
            
            if(!empty($os)){
                $condition = "";
                $condition .= $os=="j2me" ? " AND extension='jar'" : "";    
                $condition .= $os=="android" ? " AND extension='apk'" : "";    
                $condition .= $os=="ios" ? " AND extension='plist'" : "";    
                
                $sql = "SELECT filename FROM c_game_file WHERE id IN (" . $list_game_file . ") " . $condition . " AND status=1";
                $command = Yii::app()->db->createCommand($sql);
                $rowsFilename = $command->queryRow();
                if(!empty($rowsFilename)){
                    $filename = $rowsFilename["filename"];
                } 
            }    
        }
        return $filename;
    }
    
    public function UpdateDownload($gameId, $id)
    {
        $sql = "UPDATE c_game SET download=(download + 1) WHERE id=" . $gameId;
        $command = Yii::app()->db->createCommand($sql);
        $result = $command->execute();
        
        $sql = "UPDATE c_game_file SET download=(download + 1) WHERE id=" . $id;
        $command = Yii::app()->db->createCommand($sql);
        $result = $command->execute();
        
        return $result;
    }
}  
?>
